Calculated Columns

The Calculated Columns node enables users to manipulate columns during the data flow using a PQL script. Once the node has been connected to the relevant table, provide a PQL script in order to create or manipulate column values, or perform calculations on existing column values, and add these manipulations as an additional column.

While the other column operation functions offer shortcuts to standard column operations, the calculated column is more advanced. It produces a new column of values using free-form functions based on a user-defined PQL expression.

You can provide a SQL expression by writing or pasting it directly into the quick script editor, or you can open the PQL formula editor. The PQL editor exposes the data model's meta-structures, and features drag and drop functionality and intellisense, so that you can easily pick and choose the required functions and meta data.

  • Click here to learn more about the PQL editor.
  • Click here to review the 'Common' PQL functions.

Quick Script Editor

Write a PQL expression in the quick script editor (red highlight below):

  • Use square brackets to select a column
  • PQL scripts are case-sensitive - make sure that column headings are written in the correct case

Under Calculated Column Name provide a name for the new column, and under Calculate Column Type select the relevant column type (green highlight)

PQL Editor

The PQL Editor is a script-writing interface with drag and drop functionality and intellisense. The editor exposes meta structure of the data model, as well as the menu of PQL functions. Using drag and drop functionality you can construct your PQL expression by selecting the required functions and meta data.

Open the PQL Editor by clicking the Advanced Script button (blue arrow above) in the Calculated Column window.

  • Click here to learn more about the PQL editor.

Add a Calculated Column

STEP 1

Add the Calculated Column node to the canvas and connect it to the relevant table.

STEP 2

In the Script window, write your PQL script. Open the PQL editor (red arrow) to write your PQL expression by selecting the required functions, columns, variables, and elements. For details about PQL, click here.

Name your calculated column, and select the column type (green highlight).

STEP 3

Click the to preview.